﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.Serialization;
using System.Data.SqlClient;

namespace EFMagic.Schema
{
    public class EFMSchemaManager
    {
        public EFMSchema ReadFromFile(string filename)
        {
            using (FileStream s = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                IFormatter formatter = new BinaryFormatter();
                return formatter.Deserialize(s) as EFMSchema;
            }
        }

        public void SaveToFile(EFMSchema schema, string filename)
        {
            using (FileStream s = File.Open(filename, FileMode.OpenOrCreate, FileAccess.Write))
            {
                IFormatter formatter = new BinaryFormatter();
                formatter.Serialize(s, schema);
            }
        }

        public EFMSchema ReadFromDatabase(string connectionString)
        {
            EFMSchema schema = new EFMSchema();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'", conn);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        EFMTable table = new EFMTable()
                        {
                            Id = Guid.NewGuid(),
                            DBName = dr.GetString(0),
                            Name = dr.GetString(0)
                        };
                        using (SqlConnection conn2 = new SqlConnection(connectionString))
                        {
                            conn2.Open();
                            SqlCommand cmd2 = new SqlCommand(String.Format(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,
                                                            DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION
                                                            FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}'", table.DBName), conn2);
                            SqlDataReader dr2 = cmd2.ExecuteReader();
                            if (dr2.HasRows)
                            {
                                while (dr2.Read())
                                {
                                    EFMColumn column = new EFMColumn()
                                    {
                                        Id = Guid.NewGuid(),
                                        DBName = dr2.GetString(0),
                                        Name = dr2.GetString(0),
                                        DBDataType = dr2.GetString(3)
                                    };
                                    switch (column.DBDataType)
                                    {
                                        case "nvarchar":
                                        case "varchar":
                                        case "nchar":
                                        case "char":
                                            if (dr2.GetInt32(4) != -1) column.DBDataType += String.Format("({0})", dr2.GetInt32(4).ToString());
                                            else column.DBDataType += "(MAX)";
                                            column.DataType = "String";
                                            break;
                                        case "ntext":
                                        case "text":
                                        case "xml":
                                            column.DataType = "String";
                                            break;
                                        case "decimal":
                                        case "numeric":
                                            column.DBDataType += String.Format("({0},{1})", Convert.ToInt32(dr2.GetByte(5)).ToString(), dr2.GetInt32(6));
                                            column.DataType = "Decimal";
                                            break;
                                        case "money":
                                        case "smallmoney":
                                            column.DataType = "Decimal";
                                            break;
                                        case "float":
                                            column.DataType = "Double";
                                            break;
                                        case "real":
                                            column.DataType = "Single";
                                            break;
                                        case "date":
                                        case "datetime":
                                        case "smalldatetime":
                                            column.DataType = "DateTime";
                                            break;
                                        case "datetime2":
                                            column.DataType = "DateTime";
                                            column.DBDataType += String.Format("({0})", dr2.GetInt16(7).ToString());
                                            break;
                                        case "datetimeoffset":
                                            column.DataType = "DateTimeOffset";
                                            break;
                                        case "time":
                                            column.DataType = "Time";
                                            break;
                                        case "smallint":
                                            column.DataType = "Int16";
                                            break;
                                        case "int":
                                            column.DataType = "Int32";
                                            break;
                                        case "bigint":
                                            column.DataType = "Int64";
                                            break;
                                        case "tinyint":
                                            column.DataType = "Byte";
                                            break;
                                        case "binary":
                                        case "image":
                                        case "timestamp":
                                            column.DataType = "Byte[]";
                                            break;
                                        case "varbinary":
                                            column.DataType = "Byte[]";
                                            if (dr2.GetInt32(4) != -1) column.DBDataType += String.Format("({0})", dr2.GetInt32(4).ToString());
                                            else column.DBDataType += "(MAX)";
                                            break;
                                        case "bit":
                                            column.DataType = "Boolean";
                                            break;
                                        case "uniqueidentifier":
                                            column.DataType = "Guid";
                                            break;
                                    }
                                    table.Columns.Add(column.Id, column);
                                }
                            }
                            conn2.Close();
                        }
                        schema.Tables.Add(table.Id, table);
                    }
                }
                dr.Close();
                cmd.CommandText = @"SELECT     INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME, 
                                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
                                    FROM         INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
                                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON 
                                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
                                    WHERE     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE <> 'FOREIGN KEY') AND 
                                    (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE <> 'CHECK')";
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        var tablename = dr.GetString(1);
                        var columnname = dr.GetString(2);
                        var constraintname = dr.GetString(0);
                        var constrainttype = dr.GetString(3);
                        var table = (from x in schema.Tables
                                     where x.Value.DBName == tablename
                                     select x.Key);
                        if (table.Count() != 0)
                        {
                            var column = from x in schema.Tables[table.First()].Columns
                                         where x.Value.Name == columnname
                                         select x.Key;
                            if (column.Count() != 0)
                            {
                                if (schema.Tables[table.First()].Constraints.ContainsKey(constraintname))
                                {
                                    constraintname+=Guid.NewGuid().ToString().Replace('-','_');
                                }
                                if (constrainttype == "PRIMARY KEY")
                                {
                                    schema.Tables[table.First()].Constraints.Add(constraintname, new EFMPrimaryKey() { Name = constraintname, ColumnId = column.First() });
                                }
                                else
                                {
                                    schema.Tables[table.First()].Constraints.Add(constraintname, new EFMUnique() { Name = constraintname, ColumnId = column.First() });
                                }
                            }
                        }
                    }
                }
                dr.Close();
                cmd.CommandText = @"SELECT     INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, 
                                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE, 
                                    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
FROM                                INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN
                                    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON 
                                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME INNER JOIN
                                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON 
                                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME";
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        var tablename = dr.GetString(1);
                        var columnname = dr.GetString(2);
                        var constraintname = dr.GetString(0);
                        var constrainttype = dr.GetString(3);
                        var unconstraintname = dr.GetString(4);
                        var table = (from x in schema.Tables
                                     where x.Value.DBName == tablename
                                     select x.Key);
                        if (table.Count() != 0)
                        {
                            var column = from x in schema.Tables[table.First()].Columns
                                         where x.Value.Name == columnname
                                         select x.Key;
                            if (column.Count() != 0)
                            {
                                var rtable = from x in schema.Tables
                                             where x.Value.Constraints.ContainsKey(unconstraintname)
                                             select x;
                                if (rtable.Count() != 0)
                                {
                                    if (schema.Tables[table.First()].Constraints.ContainsKey(constraintname))
                                    {
                                        constraintname += Guid.NewGuid().ToString().Replace('-', '_');
                                    }
                                    schema.Tables[table.First()].Constraints.Add(constraintname,
                                        new EFMForeignKey()
                                        {
                                            Name = constraintname,
                                            ColumnId = column.First(),
                                            RTableId = rtable.First().Key,
                                            RColumnId = schema.Tables[rtable.First().Key].Constraints[unconstraintname].ColumnId
                                        });
                                }
                            }
                        }
                    }
                }
                conn.Close();
            }
            return schema;
        }
    }
}
